package cn.itcast.mybatis.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.junit.Test;

public class DemoCurd {
	
	
	@Test
	public void testInsert() {
		Connection connection = null;
		
		PreparedStatement preparedStatement = null;
		
		ResultSet resultSet = null;
		
		try {
			
			//加载数据库驱动
			Class.forName("com.mysql.jdbc.Driver");
			
			//通过数据库驱动管理连接数据库
			connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/exam?characterEcoding=utf-8","root","root");
			
			//写sql语句
			String sql = "insert into student (sid,name) values (?,?)";
			
			//通过预处理获得sql语句
			preparedStatement = connection.prepareStatement(sql);
			
			preparedStatement.setInt(1, 8);
			preparedStatement.setString(2, "周明新");
			
			//得到结果集
			int executeUpdate = preparedStatement.executeUpdate();

			System.out.println(preparedStatement);
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			closeQuiety(resultSet);
			closeQuiety(preparedStatement);
			closeQuiety(connection);
		}
		
	}
	
	@Test
	public void testDelete() {
		Connection connection = null;
		
		PreparedStatement preparedStatement = null;
		
		ResultSet resultSet = null;
		
		try {
			
			//加载数据库驱动
			Class.forName("com.mysql.jdbc.Driver");
			
			//通过数据库驱动管理连接数据库
			connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/exam?characterEcoding=utf-8","root","root");
			
			//写sql语句
			String sql = "delete from student where sid = 8";
			
			//通过预处理获得sql语句
			preparedStatement = connection.prepareStatement(sql);
			
			//得到结果集
			preparedStatement.executeUpdate();
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			closeQuiety(resultSet);
			closeQuiety(preparedStatement);
			closeQuiety(connection);
		}
		
	}
	
	@Test
	public void testUpdate() {
		Connection connection = null;
		
		PreparedStatement preparedStatement = null;
		
		ResultSet resultSet = null;
		
		try {
			
			//加载数据库驱动
			Class.forName("com.mysql.jdbc.Driver");
			
			//通过数据库驱动管理连接数据库
			connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/exam?characterEcoding=utf-8","root","root");
			
			//写sql语句
			String sql = "update student set name = '小强' where sid = 7";
			
			//通过预处理获得sql语句
			preparedStatement = connection.prepareStatement(sql);
			
			//得到结果集
			preparedStatement.executeUpdate();
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			closeQuiety(resultSet);
			closeQuiety(preparedStatement);
			closeQuiety(connection);
		}
		
	}
	/**
	 * 批处理模式
	 */
	@Test
	public void testBatchInsert() {
		
		
		
		Connection connection = null;
		
		PreparedStatement preparedStatement = null;
		
		ResultSet resultSet = null;
		
		try {
			
			//加载数据库驱动
			Class.forName("com.mysql.jdbc.Driver");
			
			//通过数据库驱动管理连接数据库
			connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/exam?characterEcoding=utf-8","root","root");
			
			//写sql语句
			String sql = "insert into student (sid,name) values (?,?)";
			
			//通过预处理获得sql语句
			preparedStatement = connection.prepareStatement(sql);
			
			for(int i =0;i<100;i++) {
				
				preparedStatement.setInt(1, 8);
				preparedStatement.setString(2, "周明新");
				//得到结果集
				preparedStatement.addBatch();
				
			}
			
			preparedStatement.executeBatch();
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			closeQuiety(resultSet);
			closeQuiety(preparedStatement);
			closeQuiety(connection);
		}
		
	}
	
	
	private static void closeQuiety(AutoCloseable closeable) {
		if(closeable!=null) {
			try {
				closeable.close();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
}
